-- demo_madang.sql
----------------------------------------
DROP TABLE book     CASCADE CONSTRAINTS;
DROP TABLE customer CASCADE CONSTRAINTS;
DROP TABLE orders   CASCADE CONSTRAINTS;
purge recyclebin;

CREATE TABLE book (
    bookid    NUMBER(2) NOT NULL,
    bookname  VARCHAR2(40),
    publisher VARCHAR2(40),
    price     NUMBER(8)
);
ALTER TABLE book ADD CONSTRAINT book_pk PRIMARY KEY ( bookid );

CREATE TABLE customer (
    custid  NUMBER(2) NOT NULL,
    name    VARCHAR2(40),
    address VARCHAR2(50),
    phone   VARCHAR2(20)
);
ALTER TABLE customer ADD CONSTRAINT customer_pk PRIMARY KEY ( custid );

CREATE TABLE orders (
    orderid   NUMBER(2) NOT NULL,
    custid    NUMBER(2) NOT NULL,
    bookid    NUMBER(2) NOT NULL,
    saleprice NUMBER(8),
    orderdate DATE
);
ALTER TABLE orders ADD CONSTRAINT orders_pk PRIMARY KEY ( orderid );

ALTER TABLE orders
    ADD CONSTRAINT relation_1 FOREIGN KEY ( bookid )
        REFERENCES book ( bookid );
ALTER TABLE orders
    ADD CONSTRAINT relation_2 FOREIGN KEY ( custid )
        REFERENCES customer ( custid );
select * from tab;
--------------------------------------
--book
----------------------------------------------------------------------------
insert into BOOK values( 1,'축구의 역사'        ,'굿스포츠'   ,  7000);
insert into BOOK values( 2,'축구아는 여자'      ,'나무수'     , 13000);
insert into BOOK values( 3,'축구의 이해'        ,'대한미디어' , 22000);
insert into BOOK values( 4,'골프 바이블'        ,'대한미디어' , 35000);
insert into BOOK values( 5,'피겨 교본'          ,'굿스포츠'   ,  8000);
insert into BOOK values( 6,'역도 단계별기술'    ,'굿스포츠'   ,  6000);
insert into BOOK values( 7,'야구의 추억'        ,'이상미디어' , 20000);
insert into BOOK values( 8,'야구를 부탁해'      ,'이상미디어' , 13000);
insert into BOOK values( 9,'올림픽 이야기'      ,'삼성당'     ,  7500);
insert into BOOK values(10,'Olympic Champions'  ,'Pearson'    , 13000);
commit;
select * from BOOK ;

--CUSTOMER
------------------------------------------------------------------------------
insert into CUSTOMER values( 1,'박지성' ,'영국 맨체스타'    ,'000-5000-0001' );
insert into CUSTOMER values( 2,'김연아' ,'대한민국 서울'    ,'000-6000-0001' );
insert into CUSTOMER values( 3,'장미란' ,'대한민국 강원도'  ,'000-7000-0001' );
insert into CUSTOMER values( 4,'추신수' ,'미국 클리블랜드'  ,'000-8000-0001' );
insert into CUSTOMER values( 5,'박세리' ,'대한민국 대전'    ,''              );
commit;
select * from CUSTOMER ;

--ORDERS -- 주문(Orders) 테이블의 책값은 할인 판매를 가정함
-----------------------------------------------------------
insert into ORDERS values(  1,  1,  1,    6000,'01-JUL-14');
insert into ORDERS values(  2,  1,  3,   21000,'03-JUL-14');
insert into ORDERS values(  3,  2,  5,    8000,'03-JUL-14');
insert into ORDERS values(  4,  3,  6,    6000,'04-JUL-14');
insert into ORDERS values(  5,  4,  7,   20000,'05-JUL-14');
insert into ORDERS values(  6,  1,  2,   12000,'07-JUL-14');
insert into ORDERS values(  7,  4,  8,   13000,'07-JUL-14');
insert into ORDERS values(  8,  3, 10,   12000,'08-JUL-14');
insert into ORDERS values(  9,  2, 10,    7000,'09-JUL-14');
insert into ORDERS values( 10,  3,  8,   13000,'10-JUL-14');
commit;
select * from ORDERS ;
----------------------------------------

-- 3장에서 사용되는 Imported_book 테이블

CREATE TABLE Imported_Book (
  bookid      NUMBER ,
  bookname    VARCHAR(40),
  publisher   VARCHAR(40),
  price       NUMBER(8) 
);
INSERT INTO Imported_Book VALUES(21, 'Zen Golf'     , 'Pearson'       , 12000);
INSERT INTO Imported_Book VALUES(22, 'Soccer Skills', 'Human Kinetics', 15000);
COMMIT;
select  *  from tab;